Relational System Modelling (OLTP)

Relational System Modelling focuses on designing databases for Online Transaction Processing (OLTP). These systems run the core, day-to-day operations of an application (e.g., an e-commerce checkout system, a banking ledger).

The primary goal of relational modelling is to ensure data integrity, eliminate redundancy, and process high volumes of rapid, small transactions securely.

1. The Core Philosophy: ACID

Relational databases (like PostgreSQL, MySQL, Oracle) are built on ACID properties, which guarantee that transactions are processed reliably:

• Atomicity: A transaction is "all or nothing." If a failure occurs mid-transaction, everything rolls back.
• Consistency: Data must always adhere to defined rules (e.g., a foreign key must point to an existing record).
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once a transaction is committed, it is saved permanently, even in a power loss.

2. Normalization

Normalization is the process of structuring a relational database to reduce data redundancy and improve data integrity. It involves dividing large, messy tables into smaller, linked tables.

First Normal Form (1NF)

• Rule: Each cell must contain a single, atomic value. No repeating groups or arrays.
• Bad: User_Table: ID=1, Name=Alice, Phones="555-1234, 555-9876"
• Good: Create a separate User_Phones table where each phone number gets its own row.

Second Normal Form (2NF)

• Rule: Must be in 1NF, and all non-key attributes must depend on the entire primary key.
• Context: This applies mostly to tables with composite primary keys (two columns acting as the PK).

Third Normal Form (3NF)

• Rule: Must be in 2NF, and there must be no transitive dependencies. (A non-key column cannot depend on another non-key column).
• Bad: Order_Table: Order_ID, User_ID, User_Email. (Email depends on User_ID, not Order_ID).
• Good: Move User_Email to a separate User_Table.

3. Entity-Relationship Diagrams (ERD)

ERDs are the standard visual language for relational modelling. They map out how tables connect via Primary Keys (PK) and Foreign Keys (FK).

Relationship Types

1. One-to-One (1:1):
   • Example: A User has one Passport_Details record.
   • Implementation: Place a User_ID as a unique Foreign Key in the Passport_Details table.
2. One-to-Many (1:N):
   • Example: A Customer has many Orders.
   • Implementation: Place Customer_ID as a Foreign Key in the Orders table.
3. Many-to-Many (M:N):
   • Example: Students enroll in many Classes, and Classes have many Students.
   • Implementation: Relational databases cannot handle direct M:N links. You must create an Associative (Junction) Table (e.g., Enrollments table with Student_ID and Class_ID).

4. Best Practices for OLTP Modelling

1. Use Surrogate Keys: Use auto-incrementing integers or UUIDs as Primary Keys instead of natural keys (like a user's email address), as emails can change.
2. Index Strategically: Create B-Tree indexes on columns heavily used in WHERE, JOIN, and ORDER BY clauses to speed up reads. (Beware: too many indexes slow down INSERT/UPDATE operations).
3. Enforce Constraints: Always use database-level constraints (NOT NULL, UNIQUE, FOREIGN KEY) to maintain data integrity, rather than relying solely on application code.
4. Avoid Over-Normalization: While 3NF is the standard, sometimes breaking the rules (denormalizing slightly) is necessary if a query requires joining 10+ tables and becomes a performance bottleneck.